﻿-- SQL Manager 2010 Lite for SQL Server 3.6.0.1
-- ---------------------------------------
-- Host      : PC03\SQLEXPRESS
-- Database  : skolenie
-- Version   : Microsoft SQL Server  10.50.1600.1


DROP USER [skolenieUsr]
GO

--
-- Dropping table ProjectEmployees : 
--

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[ProjectEmployees]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
  DROP TABLE [dbo].[ProjectEmployees]
GO

--
-- Dropping table Project : 
--

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Project]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
  DROP TABLE [dbo].[Project]
GO

--
-- Dropping table Client : 
--

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Client]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
  DROP TABLE [dbo].[Client]
GO

--
-- Dropping table Employee : 
--

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Employee]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
  DROP TABLE [dbo].[Employee]
GO

--
-- Dropping table Person : 
--

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Person]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
  DROP TABLE [dbo].[Person]
GO

--
-- Dropping table Address : 
--

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Address]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
  DROP TABLE [dbo].[Address]
GO

--
-- Dropping table Manager : 
--

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Manager]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
  DROP TABLE [dbo].[Manager]
GO

--
-- Definition for user rsdbUsr : 
--

IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = N'rsdbUsr')
BEGIN
  CREATE USER [rsdbUsr]
    FOR LOGIN  [rsdbUsr]
    WITH DEFAULT_SCHEMA = [dbo]
END
GO

--
-- Definition for user skolenieUsr : 
--

IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = N'skolenieUsr')
BEGIN
  CREATE USER [skolenieUsr]
    FOR LOGIN  [skolenieUsr]
    WITH DEFAULT_SCHEMA = [dbo]
END
GO

--
-- Definition for table Address : 
--

CREATE TABLE [dbo].[Address] (
  [ID] int IDENTITY(1, 1) NOT NULL,
  [Street] varchar(100) COLLATE Slovak_CI_AS NULL,
  [Town] varchar(50) COLLATE Slovak_CI_AS NULL,
  [ZipCode] varchar(20) COLLATE Slovak_CI_AS NULL
)
ON [PRIMARY]
GO

--
-- Definition for table Person : 
--

CREATE TABLE [dbo].[Person] (
  [ID] int IDENTITY(1, 1) NOT NULL,
  [Prefix] varchar(20) COLLATE Slovak_CI_AS NULL,
  [Name] varchar(30) COLLATE Slovak_CI_AS NULL,
  [Surname] varchar(50) COLLATE Slovak_CI_AS NULL,
  [BirthNumber] varchar(15) COLLATE Slovak_CI_AS NULL,
  [BirthDate] date NULL,
  [Gender] varchar(2) COLLATE Slovak_CI_AS NULL,
  [AddressID] int NULL
)
ON [PRIMARY]
GO

--
-- Definition for table Client : 
--

CREATE TABLE [dbo].[Client] (
  [ID] int IDENTITY(1, 1) NOT NULL,
  [PersonID] int NULL
)
ON [PRIMARY]
GO

--
-- Definition for table Manager : 
--

CREATE TABLE [dbo].[Manager] (
  [ID] int IDENTITY(1, 1) NOT NULL,
  [EmployeeID] int NULL
)
ON [PRIMARY]
GO

--
-- Definition for table Employee : 
--

CREATE TABLE [dbo].[Employee] (
  [ID] int IDENTITY(1, 1) NOT NULL,
  [PersonID] int NULL,
  [Company] varchar(100) COLLATE Slovak_CI_AS NULL,
  [Salary] float NULL,
  [ManagerID] int NULL
)
ON [PRIMARY]
GO

--
-- Definition for table Project : 
--

CREATE TABLE [dbo].[Project] (
  [ID] int IDENTITY(1, 1) NOT NULL,
  [Name] varchar(50) COLLATE Slovak_CI_AS NULL,
  [StartDate] date NULL,
  [Length] int NULL,
  [Budget] float NULL,
  [ProjectType] int NULL,
  [ManagerID] int NULL,
  [ClientID] int NULL
)
ON [PRIMARY]
GO

--
-- Definition for table ProjectEmployees : 
--

CREATE TABLE [dbo].[ProjectEmployees] (
  [ProjectID] int NOT NULL,
  [EmployeeID] int NOT NULL
)
ON [PRIMARY]
GO

--
-- Data for table dbo.Address  (LIMIT 0,500)
--

SET IDENTITY_INSERT [dbo].[Address] ON
GO

INSERT INTO [dbo].[Address] ([ID], [Street], [Town], [ZipCode])
VALUES 
  (1, N'Mala ulica', N'Bratislava', N'123456')
GO

INSERT INTO [dbo].[Address] ([ID], [Street], [Town], [ZipCode])
VALUES 
  (2, N'Velka ulica', N'Bratislava', N'123456')
GO

INSERT INTO [dbo].[Address] ([ID], [Street], [Town], [ZipCode])
VALUES 
  (3, N'Hneda ulica', N'Trnava', N'654321')
GO

INSERT INTO [dbo].[Address] ([ID], [Street], [Town], [ZipCode])
VALUES 
  (4, N'Hurbanova ulica', N'Zilina', N'334433')
GO

INSERT INTO [dbo].[Address] ([ID], [Street], [Town], [ZipCode])
VALUES 
  (5, N'Mrkvovy sad', N'Hurbanovo', N'112233')
GO

SET IDENTITY_INSERT [dbo].[Address] OFF
GO

--
-- Data for table dbo.Client  (LIMIT 0,500)
--

SET IDENTITY_INSERT [dbo].[Client] ON
GO

INSERT INTO [dbo].[Client] ([ID], [PersonID])
VALUES 
  (1, 2)
GO

SET IDENTITY_INSERT [dbo].[Client] OFF
GO

--
-- Data for table dbo.Employee  (LIMIT 0,500)
--

SET IDENTITY_INSERT [dbo].[Employee] ON
GO

INSERT INTO [dbo].[Employee] ([ID], [PersonID], [Company], [Salary], [ManagerID])
VALUES 
  (1, 1, N'Firma a.s.', 600, 1)
GO

INSERT INTO [dbo].[Employee] ([ID], [PersonID], [Company], [Salary], [ManagerID])
VALUES 
  (2, 3, N'Firma a.s.', 650, 1)
GO

INSERT INTO [dbo].[Employee] ([ID], [PersonID], [Company], [Salary], [ManagerID])
VALUES 
  (3, 4, N'Firma a.s.', 600, 1)
GO

INSERT INTO [dbo].[Employee] ([ID], [PersonID], [Company], [Salary], [ManagerID])
VALUES 
  (4, 5, N'Firma a.s.', 800, 2)
GO

SET IDENTITY_INSERT [dbo].[Employee] OFF
GO

--
-- Data for table dbo.Manager  (LIMIT 0,500)
--

SET IDENTITY_INSERT [dbo].[Manager] ON
GO

INSERT INTO [dbo].[Manager] ([ID], [EmployeeID])
VALUES 
  (1, 4)
GO

INSERT INTO [dbo].[Manager] ([ID], [EmployeeID])
VALUES 
  (2, 2)
GO

SET IDENTITY_INSERT [dbo].[Manager] OFF
GO

--
-- Data for table dbo.Person  (LIMIT 0,500)
--

SET IDENTITY_INSERT [dbo].[Person] ON
GO

INSERT INTO [dbo].[Person] ([ID], [Prefix], [Name], [Surname], [BirthNumber], [BirthDate], [Gender], [AddressID])
VALUES 
  (1, N'X', N'Janko', N'Hrasko', N'801111/1234', N'1980-11-11', N'M', 1)
GO

INSERT INTO [dbo].[Person] ([ID], [Prefix], [Name], [Surname], [BirthNumber], [BirthDate], [Gender], [AddressID])
VALUES 
  (2, N'X', N'Ferko', N'Pilcik', N'701212/1234', N'1970-12-12', N'M', 2)
GO

INSERT INTO [dbo].[Person] ([ID], [Prefix], [Name], [Surname], [BirthNumber], [BirthDate], [Gender], [AddressID])
VALUES 
  (3, N'X', N'Anicka', N'Cervena', N'855101/1234', N'1985-01-01', N'F', 3)
GO

INSERT INTO [dbo].[Person] ([ID], [Prefix], [Name], [Surname], [BirthNumber], [BirthDate], [Gender], [AddressID])
VALUES 
  (4, N'X', N'Adam', N'Sangala', N'751010/1234', N'1975-10-10', N'M', 4)
GO

INSERT INTO [dbo].[Person] ([ID], [Prefix], [Name], [Surname], [BirthNumber], [BirthDate], [Gender], [AddressID])
VALUES 
  (5, N'X', N'Peter', N'Velky', N'650909/1234', N'1965-09-09', N'M', 5)
GO

SET IDENTITY_INSERT [dbo].[Person] OFF
GO

--
-- Data for table dbo.Project  (LIMIT 0,500)
--

SET IDENTITY_INSERT [dbo].[Project] ON
GO

INSERT INTO [dbo].[Project] ([ID], [Name], [StartDate], [Length], [Budget], [ProjectType], [ManagerID], [ClientID])
VALUES 
  (2, N'Prvy projekt', N'2011-01-01', 10, 1000, 1, 1, 1)
GO

INSERT INTO [dbo].[Project] ([ID], [Name], [StartDate], [Length], [Budget], [ProjectType], [ManagerID], [ClientID])
VALUES 
  (3, N'Druhy projekt', N'2011-12-15', 100, 5000, 2, 2, 1)
GO

SET IDENTITY_INSERT [dbo].[Project] OFF
GO

--
-- Data for table dbo.ProjectEmployees  (LIMIT 0,500)
--

INSERT INTO [dbo].[ProjectEmployees] ([ProjectID], [EmployeeID])
VALUES 
  (2, 1)
GO

INSERT INTO [dbo].[ProjectEmployees] ([ProjectID], [EmployeeID])
VALUES 
  (2, 2)
GO

INSERT INTO [dbo].[ProjectEmployees] ([ProjectID], [EmployeeID])
VALUES 
  (2, 3)
GO

INSERT INTO [dbo].[ProjectEmployees] ([ProjectID], [EmployeeID])
VALUES 
  (3, 4)
GO

INSERT INTO [dbo].[ProjectEmployees] ([ProjectID], [EmployeeID])
VALUES 
  (3, 2)
GO

--
-- Definition for indices : 
--

ALTER TABLE [dbo].[Address]
ADD PRIMARY KEY CLUSTERED ([ID])
WITH (
  PAD_INDEX = OFF,
  IGNORE_DUP_KEY = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

ALTER TABLE [dbo].[Person]
ADD PRIMARY KEY CLUSTERED ([ID])
WITH (
  PAD_INDEX = OFF,
  IGNORE_DUP_KEY = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

ALTER TABLE [dbo].[Client]
ADD PRIMARY KEY CLUSTERED ([ID])
WITH (
  PAD_INDEX = OFF,
  IGNORE_DUP_KEY = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

ALTER TABLE [dbo].[Manager]
ADD PRIMARY KEY CLUSTERED ([ID])
WITH (
  PAD_INDEX = OFF,
  IGNORE_DUP_KEY = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

ALTER TABLE [dbo].[Employee]
ADD PRIMARY KEY CLUSTERED ([ID])
WITH (
  PAD_INDEX = OFF,
  IGNORE_DUP_KEY = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

ALTER TABLE [dbo].[Project]
ADD PRIMARY KEY CLUSTERED ([ID])
WITH (
  PAD_INDEX = OFF,
  IGNORE_DUP_KEY = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

--
-- Definition for foreign keys : 
--

ALTER TABLE [dbo].[Person]
ADD CONSTRAINT [Person_fk] FOREIGN KEY ([AddressID]) 
  REFERENCES [dbo].[Address] ([ID]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

ALTER TABLE [dbo].[Client]
ADD CONSTRAINT [Client_fk] FOREIGN KEY ([PersonID]) 
  REFERENCES [dbo].[Person] ([ID]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

ALTER TABLE [dbo].[Manager]
ADD CONSTRAINT [Manager_fk] FOREIGN KEY ([EmployeeID]) 
  REFERENCES [dbo].[Employee] ([ID]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

ALTER TABLE [dbo].[Employee]
ADD CONSTRAINT [Employee_fk] FOREIGN KEY ([PersonID]) 
  REFERENCES [dbo].[Person] ([ID]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

ALTER TABLE [dbo].[Employee]
ADD CONSTRAINT [Employee_fk2] FOREIGN KEY ([ManagerID]) 
  REFERENCES [dbo].[Manager] ([ID]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

ALTER TABLE [dbo].[Project]
ADD CONSTRAINT [Project_fk] FOREIGN KEY ([ManagerID]) 
  REFERENCES [dbo].[Manager] ([ID]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

ALTER TABLE [dbo].[Project]
ADD CONSTRAINT [Project_fk2] FOREIGN KEY ([ClientID]) 
  REFERENCES [dbo].[Client] ([ID]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

ALTER TABLE [dbo].[ProjectEmployees]
ADD CONSTRAINT [ProjectEmployees_fk] FOREIGN KEY ([ProjectID]) 
  REFERENCES [dbo].[Employee] ([ID]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

ALTER TABLE [dbo].[ProjectEmployees]
ADD CONSTRAINT [ProjectEmployees_fk2] FOREIGN KEY ([ProjectID]) 
  REFERENCES [dbo].[Project] ([ID]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

--
-- Role Membership
--

EXEC sp_addrolemember 'db_owner', 'skolenieUsr'
GO

EXEC sp_addrolemember 'db_accessadmin', 'skolenieUsr'
GO

EXEC sp_addrolemember 'db_securityadmin', 'skolenieUsr'
GO

EXEC sp_addrolemember 'db_ddladmin', 'skolenieUsr'
GO

EXEC sp_addrolemember 'db_backupoperator', 'skolenieUsr'
GO

EXEC sp_addrolemember 'db_datareader', 'skolenieUsr'
GO

EXEC sp_addrolemember 'db_datawriter', 'skolenieUsr'
GO

EXEC sp_addrolemember 'db_denydatareader', 'skolenieUsr'
GO

EXEC sp_addrolemember 'db_denydatawriter', 'skolenieUsr'
GO

